﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VentaElectrodomesticos.Contrato;
using System.Data.OleDb;
using System.Reflection;
using System.Data.SqlClient;
using System.Data;

namespace VentaElectrodomesticos.Datos
{
    public class BaseDAO<T> where T : Entity, new()
    {
        #region IDAO Members

        public virtual bool save(T entity)
        {
            if (this.exists(entity))
                return this.update(entity);
            else
                return this.insert(entity);            
        }

        public virtual List<T> getAll()
        {
            return this.getAll(this.getTableView(), false);
        }

        public virtual List<T> getAllActives()
        {
            return this.getAll(this.getTableView(), true);
        }

        protected virtual List<T> getAll(string tableView, bool onlyAvailable) {
            T entity = new T();
            String sql = new QueryBuilder<T>(entity, tableView).getSqlGetByAll(onlyAvailable);
            SqlDataReader reader = this.getRows(sql);
            return makeObjects(ref entity, reader);
        }

        public virtual List<T> getAll(T filter)
        {
            return this.getAll(filter, this.getDefaultStoreProcedureName(filter), this.getCustomFilterParameters(filter));
        }

        protected virtual List<T> getAll(T filter, string storedProcedureName, List<SqlParameter> customParameters)
        {
            T entity = new T();
            List<SqlParameter> parameters = new QueryBuilder<T>(filter, this.getTableView()).getStoredProcedureParameters();           
            if (customParameters != null) {
                parameters.AddRange(customParameters.ToArray());
            }
            SqlDataReader reader = this.executeStoredProcedure(Config.tableSpace + storedProcedureName, parameters);
            return makeObjects(ref entity, reader);
        }

        protected string getDefaultStoreProcedureName(T filter) {
            return Config.StoredProceduredPrefix + filter.GetType().Name;
        }

        protected virtual List<SqlParameter> getCustomFilterParameters(T filter)
        {
            return null;

        }
        protected List<T> makeObjects(ref T entity, SqlDataReader reader)
        {
            List<T> entities = new List<T>();
            while (reader.Read())
            {
                entity = new T();
                entities.Add((T)this.makeObject(entity, reader));
            }
            return entities;
        }

        protected T makeObject(T entity, SqlDataReader reader)
        {            
            entity = new ObjectBuilder<T>(entity).getObject(reader);            
            return entity;
        }

        public virtual T getById(T entity)
        {
            String sql = new QueryBuilder<T>(entity, this.getTableView()).getSqlGetById();
            SqlDataReader reader = this.getRows(sql);
            if (reader.Read())
            {
                entity = this.makeObject(entity, reader);
            }
            reader.Close();
            return entity;
        }

        public virtual bool delete(T entity)
        {
            String sql = new QueryBuilder<T>(entity, this.getTableName()).getSqlDelete(this.useLogicalDelete());
            return this.execute(sql);
        }

        public virtual bool update(T entity)
        {
            String sql = new QueryBuilder<T>(entity, this.getTableName()).getSqlUpdate();
            return this.execute(sql);            
        }

        public virtual bool insert(T entity)
        {
            String sql = new QueryBuilder<T>(entity, this.getTableName()).getSqlInsert();
            return this.execute(sql);            
        }

        public virtual int? insertAndReturnId(T entity)
        {
            String sql = new QueryBuilder<T>(entity, this.getTableName()).getSqlInsertAndReturnId();
            return this.executeEscalar(sql);
        }

        public virtual bool exists(T entity)
        {            
            return entity.Id > 0;
        }

        #endregion

        private SqlCommand getSqlCommand(String sql)
        {
            SqlConnection conn = Connection.getConnection();
            return new SqlCommand(sql, conn);
        }

        protected bool execute(String sql)
        {
            try {
                SqlCommand command = this.getSqlCommand(sql);
                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
                return true;
            }
            catch (SqlException)
            {
                throw new DatosException("Error de base de datos. Comuniquese con el administrador del sistema.");
            }
            catch (InvalidOperationException)
            {
                throw new DatosException("Error de base de datos. Comuniquese con el administrador del sistema.");
            }
        }

        protected SqlDataReader getRows(String sql)
        {
            try
            {
                SqlDataReader reader;
                SqlCommand command = this.getSqlCommand(sql);
                command.Connection.Open();
                reader = command.ExecuteReader();
                return reader;
            }
            catch (SqlException)
            {
                throw new DatosException("Error de base de datos. Comuniquese con el administrador del sistema.");
            }
            catch (InvalidOperationException)
            {
                throw new DatosException("Error de base de datos. Comuniquese con el administrador del sistema.");
            }
            
        }

        protected int? executeEscalar(String sql)
        {   
            try{
                SqlCommand command = this.getSqlCommand(sql);
                command.Connection.Open();
                object obj = command.ExecuteScalar();
                if (obj == null)
                    return null;
                int value = Convert.ToInt32(obj);
                command.Connection.Close();
                return value;

            }
            catch (SqlException)
            {
                throw new DatosException("Error de base de datos. Comuniquese con el administrador del sistema.");
            }
            catch (InvalidOperationException)
            {
                throw new DatosException("Error de base de datos. Comuniquese con el administrador del sistema.");
            }
        }

        protected SqlDataReader executeStoredProcedure(String name, List<SqlParameter> parameters)
        {
            try {
                SqlDataReader reader;
                SqlCommand command = this.getSqlCommand(name);
                command.Connection.Open();
                command.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter param in parameters)
                    command.Parameters.Add(param);
                reader = command.ExecuteReader();
                return reader;
            }
            catch (SqlException)
            {
                throw new DatosException("Error de base de datos. Comuniquese con el administrador del sistema.");
            }
            catch (InvalidOperationException)
            {
                throw new DatosException("Error de base de datos. Comuniquese con el administrador del sistema.");
            }
        }

        protected virtual String getTableName() {
            return null;    
        }
           
        protected virtual String getTableView()
        {
            return this.getTableName();
        }

        protected virtual bool useLogicalDelete() {
            return true;
        }

    }
}
